<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Media Manager - report page</title>
<link rel="stylesheet" type="text/css" href="mmmanager.css" />
<link rel="shortcut icon" href="../favicon.ico" />
</head>
<body>

<h1>Media record report page</h1>
<a href='index.php'>Back</a>

<?php
setlocale(LC_MONETARY, 'en_CA');
require 'connect.php';

//generate summary table

$sqlSelectMediaInfo = "SELECT title, author1, cost, paid, publisher, year, ISBN, type, currency_id FROM 
media m LEFT OUTER JOIN author a ON m.author_id = a.author_id
LEFT OUTER JOIN publisher p ON m.publisher_id = p.publisher_id
LEFT OUTER JOIN cost c ON m.cost_id = c.cost_id
ORDER BY type, author1, title";
$sqlSelectTable = mysqli_query($mysqli,$sqlSelectMediaInfo);
//get sum values
$sqlSelectSum = "SELECT round(sum(cost),2),round(sum(paid),2) FROM media m LEFT OUTER JOIN cost c 
ON m.cost_id = c.cost_id";
$sqlSelectSumTable = mysqli_query($mysqli,$sqlSelectSum);
$sums = mysqli_fetch_row($sqlSelectSumTable);

echo "<table>";
echo "<tr><th>Title</th><th>Author1</th><th>Cost</th><th>Paid</th><th>Publisher</th><th>Year</th>
<th>ISBN</th><th>Type</th></tr>";
while ($row=mysqli_fetch_array($sqlSelectTable))
	{
	$currency = $row[8];
	$cost = $row[2];
	$paid = $row[3];
	
	if ($currency == 4)
	{
	//multiply by exchange rate
	$cost = round($cost * 1.5,2);
	$paid = round($paid * 1.5,2);
	}
	else if ($currency == 7)
	{
	//multiply by exchange rate
	$cost = round($cost / 10,2);
	$paid = round($paid / 10,2);
	}
	else if ($currency == 3)
	{
	//multiply by exchange rate
	$cost = round($cost / 100,2);
	$paid = round($paid / 100,2);
	
	}
	echo "<tr>";
	echo "<td>".$row[0]."</td>";
	echo "<td>".$row[1]."</td>";
	echo "<td>".money_format('%n', $cost)."</td>";//cost
	echo "<td>".money_format('%n', $paid)."</td>";//paid
	echo "<td>".$row[4]."</td>";
	echo "<td>".$row[5]."</td>";
	echo "<td><a target=_blank href='https://www.google.com/search?q=".$row[6]."&btnG=Search+Books&tbm=bks&tbo=1'>".$row[6]."</a></td>";
	echo "<td>".$row[7]."</td>";
	echo "</tr>"; 	
	}
echo "<tr><td></td><td><b>Summary</b></td><td>$".$sums[0]."</td><td>$".$sums[1]."</td><td></td><td></td><td></td><td></td></tr>";	
echo "</table>";



?>